/**
 * WorxConnected.java
 *
 * Create: Mar 20, 2006
 * David Connell
 * Copyright (c) 2005 Synergy Systems, Inc.
 */
package org.puresilk.afx.data;

// java support
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import org.puresilk.afx.SilkAfxException;
// framework support
import org.puresilk.afx.util.SilkAfxKeyManager;

/**
 * Common ancestor for all database connected 
 * classes that acts as a facade around the 
 * dynamic WorxConnector objects stored in
 * the application namespace of the local
 * Key Manager.
 */
public class SilkAfxConnected {
	private String AFX_SVC_PREFIX = null; 
	private String AFX_DEF_CONNECTOR = null;
	
	/**
	 * Extend in ancestor to set / override 
	 * connector names from worx.properies file 
	 */
	public SilkAfxConnected() {
		setServicePrefix("SilkAfx");
	}
	
	
	/**
	 * Add to the connections for this service...
	 * 
	 * @param cnxName
	 */
	protected void setupConnector(String cnxName){
		SilkAfxKeyManager.getInstance().setKey(getServicePrefix() + "." + cnxName,new SilkAfxConnector(cnxName));
	}
	
	/**
	 * Get the default worx connector.
	 * 
	 * @return
	 */
	protected SilkAfxConnector getConnector( ) {
		return getConnector(getConnectorName());
	}
	
	/**
	 * Get specific connector for DB access.
	 * 
	 * @param nm
	 * @return
	 */
	protected SilkAfxConnector getConnector(String nm) {
		String afxName = getServicePrefix() + "." + nm;
		

		SilkAfxKeyManager afxMgr = SilkAfxKeyManager.getInstance();
		SilkAfxConnector afxConn = (SilkAfxConnector)afxMgr.getKey(afxName);
		
		return afxConn;
	}
	
	/**
	 * Get a database connection from the 
	 * default JNDI Pool....
	 * 
	 * @return
	 */
	protected Connection getConnection(){
		return getConnection(getConnectorName());
	}
	
	/**
	 * Get a database connection from a 
	 * named JNDI Pool....
	 * 
	 * @return
	 */
	protected Connection getConnection(String cxnName){

		Connection dbConn = null;
		SilkAfxConnector afxConn = getConnector(cxnName);
		dbConn = afxConn.getConnection();
		
		return dbConn;
	}
	
	/**
	 * Return a database connection to the 
	 * control servlet....
	 */
	protected synchronized void freeConnection(Connection conn){
		SilkAfxConnector afxConn = getConnector();
		afxConn.freeConnection(conn);
	}
	
	/**
	 * Get the next table id from a db sequence.
	 * 
	 * Warning:  PostgreSQL Specific functionality
	 *  
	 * @param seqName
	 * @return next key
	 */
	public int getNextId(String seqName) {
		SilkAfxConnector afxConn = getConnector();
		return afxConn.getNextId(seqName);
	}

	/**
	 * Process stored procedure that returns 
	 * an integer value.  Used to call insert 
	 * procs that return the resulting rkey.
	 * 
	 * @param sql
	 * @throws DWMSException
	 * @return The row key of the inserted record
	 */
	public int execInsertProc(String sql) throws SilkAfxException {
		SilkAfxConnector afxConn = getConnector();
		return afxConn.execInsertProc(sql);
	}
	
	/**
	 * Process no results SQL statements
	 * (straight pass thru)
	 * 
	 * @param sql
	 * @throws SilkAfxException
	 */
	public void execSql(String sql) throws SilkAfxException {
		SilkAfxConnector afxConn = getConnector();
		afxConn.execSql(sql);
	}

	/**
	 * Process no results SQL Procedure.  Pass
	 * in a formatted procedure call,  The underlying
	 * machinery will preppend a 'Select' if none is
	 * found.  (For a Set Returing Function (SRF) you will 
	 * need to put the 'Select * From' piece in before
	 * calling this method.)
	 * 
	 * @param sql
	 * @throws DWMSException
	 */
	public void execSqlProc(String sql) throws SilkAfxException {
		SilkAfxConnector afxConn = getConnector();
		afxConn.execSqlProc(sql);
	}
		
	/**
	 * Produce disconnected result set....
	 * 
	 * @return
	 */
	public SilkAfxResultSet fetch(String sql,Vector<String> flds){
		// get the plumbing ready
		SilkAfxConnector afxConn = getConnector();
		Connection conn = afxConn.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		// create results container
		SilkAfxResultSet rv = new SilkAfxResultSet();
		
		try{
			// execute the query
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				// create a row container
				SilkAfxRow row = new SilkAfxRow();
				
				Iterator itr = flds.iterator();
				while(itr.hasNext()){
					// move each named field to row
					String fld = (String)itr.next();
					row.put(fld,rs.getObject(fld));
				}
				// add row to results
				rv.add(row);
			}
		}
		catch (Exception ex){
			// log the error and return null
			rv = null;
		}
		
		// clean up!!
		try { rs.close(); } catch (Exception e) {}
		try { st.close(); } catch (Exception e) {}
		
		afxConn.freeConnection(conn);
		
		// and done...
		return rv;
	}
	
	/**
	 *  retrieves an application initialization property value
	 */
	protected String getProperty(String key){
		
		SilkAfxKeyManager km = SilkAfxKeyManager.getInstance();
		return km.getProperty(key);
	}

	/** Domain namespace prefix */
	protected void setServicePrefix(String pref) {
		AFX_SVC_PREFIX = pref;
	}
	protected String getServicePrefix( ) {
		return AFX_SVC_PREFIX;
	}
	
	/** default database connector name */
	protected void setConnectorName(String nm){
		AFX_DEF_CONNECTOR = nm;
	}
	protected String getConnectorName(){
		return AFX_DEF_CONNECTOR;
	}

	/**
	 * Creates HashMap containg list from SQL statement.  Depends on
	 * SQL returning columns named rkey and name.  rkey is first
	 * converted to a string, then used as the 'key' for the HashMap when
	 * storing the name column as the 'value'.
	 * 
	 * @param sql
	 * @return
	 */
	public Map<String, Object> sql2map(String sql, boolean keyIsInt ) {
		// return containers
		HashMap<String,Object> rv = new HashMap<String,Object>();
		Vector<String> order = new Vector<String>();

		// query plumbing
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		
		try {
			// build connection
			conn = getConnection ( );
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			
			// read listing into hashmap
			while (rs.next()) {
				String key = null;
				
				// check it out
				if (keyIsInt)
					key = Integer.toString(rs.getInt("key"));
				else
					key = rs.getString("key");	
					
				// add to results
				String value = rs.getString("value");
				rv.put(key,value);
				order.add(key);
			}

			// add in sequencer...			
			rv.put("Order",order);
			
			// clean up						
			rs.close();
		}
		catch (Exception ex){
			rv=null;
		}
		// clean up
		if(rs != null)try{ rs.close();}catch(Exception e){}
		if(st != null)try{ st.close();}catch(Exception e){}
		freeConnection(conn);
						
		// return the results....
		return rv;
	}
	
	/**
	 * Replaces embedded apostrophes with two
	 * apostrophes in SQL strings to avoid 
	 * database errors.
	 * 
	 * @param data
	 * @return
	 */
	public String scrub(String data){
		return data.replaceAll("'","''");
	}
}
